using NUnit.Framework;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Configuration;
using System.Transactions;
using RomanJendrusz.Amazon.ETL.Infrastructure.DataBase;

namespace AmazonTests.Integration.Amazon.ETL.DomainPersistence
{
    public abstract class DataContextTestBase
    {
        private static bool DataBaseWasInitialized;

        private DataContext currentContext;
        private TransactionScope scope;
        private string MappingSource = @"Integration\Configuration\dbMapping.xml";


        public TransactionScope Scope
        {
            get { return scope; }
        }

        protected DataContext Context
        {
            get { return currentContext; }
        }

        [SetUp]
        public void SetUpDatabase()
        {
            currentContext = CreateContext();
            if (DataBaseWasInitialized == false)
            {
                var cmd =
                    @"DECLARE @i INT
                    DECLARE @error INT
                    DECLARE @x INT
                    declare @command nvarchar(max)

                    CREATE TABLE #LIVE_SPIDS (nid INT IDENTITY(1,1), spid INT NOT NULL)
                    SET @error = 0
                    SET @i = 1

                    INSERT INTO #LIVE_SPIDS
                    SELECT 
	                    spid
                    FROM Master.dbo.SYSPROCESSES
                    WHERE dbid IN (SELECT dbid FROM Master.dbo.SYSDATABASES WHERE name = 'FakeDB')
	                    AND spid <> @@SPID

                    SET @x = (SELECT MIN(nid) FROM #LIVE_SPIDS)

                    WHILE @x <= (SELECT MAX(nid) FROM #LIVE_SPIDS)
                    BEGIN

	                    SET @command = 'KILL ' + CONVERT(VARCHAR, (SELECT spid FROM #LIVE_SPIDS WHERE nid = @x))

	                    EXECUTE sp_executesql @command--, NO OUTPUT
	                    SET @error = @error + @@ERROR

	                    SET @x = @x + 1

                    END
                    IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'FakeDB')
 	            DROP Database FakeDB";
                using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["master"].ConnectionString))
                {
                    var command = new SqlCommand(cmd, con);
                    con.Open();
                    command.ExecuteNonQuery();
                }
                CreateDataBaseContext().CreateDatabase();
                DataBaseWasInitialized = true;
            }
            Setup();
        }

        protected DataContext CreateContext()
        {
            return CreateDataBaseContext().DataContext;
        }

        protected IDataBaseContext CreateDataBaseContext()
        {
            return new DataBaseContext(new FileMappingSourceBuilder(MappingSource),
                                                  new DataBaseConnectionFactory(ConfigurationManager.ConnectionStrings["fakeDB"].ConnectionString));
        }

        [TearDown]
        public void TearDownDataBase()
        {
            TearDown();
            if(scope != null)
            {
                scope.Dispose();   
            }
        }


        protected virtual void Setup()
        {
            scope = new TransactionScope();
        }

        protected virtual void TearDown()
        {
        }
    }
}